package com.ruoyi.business.service.impl;

import com.ruoyi.business.constant.IBusinessConstant;
import com.ruoyi.business.service.ISqlService;
import com.ruoyi.business.utils.TemplateHelper;
import freemarker.ext.beans.CollectionModel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Service
public class SqlService implements ISqlService {
    @Override
    public List<Map<String, Object>> search(Connection connection, String sql, Map<String, Object> parameterMap) throws Exception {
        ResultSet results = null;
        List<Map<String, Object>> mapList = null;
        String orgSql = "";
        String questionSql ="";
        List<CollectionModel> childCollection= null;
        try {
            Map dataMap = parameterMap;
            orgSql = sql;
            log.info("=========orgSql=========");
            log.info(orgSql);
//            childCollection = pickCollections(orgSql);
//            orgSql = eliminateCollections(orgSql);
            String dynamicProcessIfSql = dynamicProcessIfs(orgSql,dataMap);
//            String dynamicProcessIfSql = TemplateHelper.createContentByString(dataMap,orgSql, IBusinessConstant.ENCODE_UTF_8);
            questionSql = getReplaceVarToQuestion(dynamicProcessIfSql);
            log.info("=========questionSql=========");
            log.info(questionSql);
            List<String> keysList = getVarKeys(dynamicProcessIfSql);
            PreparedStatement ps = connection.prepareStatement(questionSql);
            try {
                for (int i = 0; i < keysList.size(); i++) {
                    ps.setObject(i + 1, dataMap.get(keysList.get(i)));
                }
                results = ps.executeQuery();
                ResultSetMetaData rsmd = results.getMetaData();
                Map<String, Object> map = null;
                mapList = new ArrayList<>();
                  while (results.next()) {
                    map = new HashMap<String, Object>();

                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
//                        map.put(WordConvertUtil.underline2Camel(rsmd.getColumnName(i),true), results.getString(rsmd.getColumnName(i)));
                        map.put(rsmd.getColumnName(i), results.getString(rsmd.getColumnName(i)));
                    }
                    //循环执行子查询
//                    if(!CollectionUtils.isEmpty(childCollection)){
//                        for(CollectionModel model:childCollection) {
//                            map.put(model.getProperty(),search(connection,tplFileName,model.getSelectId(),replaceVarByMap(model.getParameter(),map)));
//                        }
//                    }
                    mapList.add(map);
                }
                return mapList;
            } finally {
                try {
                    if (results != null) {
                        results.close();
                    }

                    if (ps != null) {
                        ps.close();
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                    log.error("关闭报错", e);
                }
            }
        } catch (Exception e) {
            log.error("多条查询报错,sql:" + questionSql, e);
            throw e;
        }
    }

    @Override
    public List<Map<String, Object>> searchPage(Connection connection, String sql, Map<String, Object> parameterMap, int pageNo, int pageSize) throws Exception {
        return null;
    }
    /**
     * 动态处理if段
     * @param sql
     * @param map
     * @return
     */
    private String dynamicProcessIfs(String sql, Map map) throws Exception {
        String regEx ="(<#if)(.*?)(</#if>)";
        Pattern r = Pattern.compile(regEx,Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()){
            if(m.groupCount()>0) {
                //System.out.println(m.group(0));
                String exp = dynamicProcessIf(m.group(0),map);
                //替换exp正则表达式
//                exp = replaceExpKeyExp(exp);
//                System.out.println("exp:"+exp);
                if(exp.equals(IBusinessConstant.ONEEQONE)) {
                    m.appendReplacement(sb, exp);
                }else{
                    m.appendReplacement(sb, escapeExprSpecialWord(pickConditionExp(m.group(0))));
                }

            }
        }
        m.appendTail(sb);
//        System.out.println(sb.toString());
        return sb.toString();
    }
    /**
     * 转义正则特殊字符 （$()*+.[]?\^{},|）
     *
     * @param keyword
     * @return
     */
    public  String escapeExprSpecialWord(String keyword) {
        if (!com.ruoyi.common.core.utils.StringUtils.isEmpty(keyword)) {
            String[] fbsArr = { "\\", "$", "(", ")", "*", "+", ".", "[", "]", "?", "^", "{", "}", "|","\r","\n" };
            for (String key : fbsArr) {
                if (keyword.contains(key)) {
//                    if(!key.equals("\r") && !key.equals("\n")) {
                    keyword = keyword.replace(key, "\\" + key);
//                    }else{
//                        keyword = keyword.replace(key, "\\\\\\" + key);
//                    }
                }
            }
        }
        return keyword;
    }
    /**
     * 提取<#if ...>exp</#if>中的exp
     * @param exp
     * @return
     */
    private String pickConditionExp(String exp) {
        String regEx ="(>)(.*?)(</#if>)";
        Pattern r = Pattern.compile(regEx,Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(exp);
        while (m.find()){
            //m.appendReplacement(sb,"?");
            if(m.groupCount()>0) {
                return  m.group(2);
            }
        }
        return "";
    }

    /**
     * 替换正则表达式关键字
     * @param exp
     * @return
     */
    private String replaceExpKeyExp(String exp) {
        exp = exp.replaceAll("\\{","\\\\{");
        exp = exp.replaceAll("\\}","\\\\}");
        exp = exp.replaceAll("\\(","\\\\(");
        exp = exp.replaceAll("\\)","\\\\)");
        exp = exp.replaceAll("\\$", "\\\\\\$");
        return exp;
    }

    /**
     * 解析单个if段
     * @param seg if代码段
     * @param map
     * @return
     */
    private String dynamicProcessIf(String seg, Map map) throws Exception {
        String retVal= TemplateHelper.createContentByString(map,seg,IBusinessConstant.ENCODE_UTF_8);
        if(com.ruoyi.common.core.utils.StringUtils.isEmpty(retVal)){
            return  IBusinessConstant.ONEEQONE;
        }else {
            return retVal;
        }
    }

    /**
     * 逻辑段计算  目前暂支持 (xxx??) 格式
     * @param logicSeg
     * @param map
     * @return
     */
    private boolean calculateLogic(String logicSeg, Map map) throws Exception {
        int leftBracketsPos = logicSeg.indexOf("(");
        int rightBracketsPos = logicSeg.indexOf(")");
        int doubleQuestionPos = logicSeg.indexOf("??");

        if(leftBracketsPos==-1||rightBracketsPos==-1||doubleQuestionPos==-1)
            throw new Exception("无效的判断逻辑表达式:"+logicSeg);
        //提取属性名
        String propertyName = logicSeg.substring(leftBracketsPos+1,doubleQuestionPos);
        if(StringUtils.hasText(propertyName)){
            if(map.containsKey(propertyName.trim())){
                return true;
            }
        }
        return false;
    }
    /**
     * 替换变量字符串中变量
     *
     * @param str 原始字符串
     * @param map 包含变量值
     * @return
     */
    public String replaceVarByMap(String str,Map map) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            if (m.groupCount() >= 3) {
                //System.out.println(m.group(2));
                m.appendReplacement(sb, (String)map.get(m.group(2)));
            }
        }
        m.appendTail(sb);
        return sb.toString();
    }
    /**
     * 替换为带？的sql
     *
     * @param sql 原始sql
     * @return
     */
    public String getReplaceVarToQuestion(String sql) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "?");
        }
        m.appendTail(sb);
        return sb.toString();
    }
    /**
     * 获取sql变量key列表
     *
     * @param orgSql
     * @return
     */
    public List<String> getVarKeys(String orgSql) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(orgSql);
        List<String> replaceGroups = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        List<String> retKeys = new ArrayList<>();
        while (m.find()) {
            m.appendReplacement(sb, "?");
            if (m.groupCount() >= 3) {
                //System.out.println(m.group(2));
                retKeys.add(m.group(2));
            }
        }
        return retKeys;
    }
}
